import { mysqlPool } from './db';
import { Document, ChatSession, ChatMessage, User } from '@/types';
import { RowDataPacket, ResultSetHeader } from 'mysql2';

// 文档操作
export class DocumentOperations {
  // 创建文档
  static async create(
    document: Omit<Document, 'id' | 'upload_time'>
  ): Promise<string> {
    const connection = await mysqlPool.getConnection();
    try {
      const [result] = await connection.execute<ResultSetHeader>(
        `INSERT INTO documents (title, content, file_path, file_type, file_size, user_id, metadata) 
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [
          document.title,
          document.content,
          document.file_path || null,
          document.file_type,
          document.file_size,
          document.user_id || null,
          JSON.stringify(document.metadata || {}),
        ]
      );

      // 获取插入的文档ID
      const [rows] = await connection.execute<RowDataPacket[]>(
        'SELECT id FROM documents WHERE id = LAST_INSERT_ID()'
      );

      return rows[0]?.id || '';
    } finally {
      connection.release();
    }
  }

  // 获取文档列表
  static async getAll(
    userId?: string,
    limit = 50,
    offset = 0
  ): Promise<Document[]> {
    const connection = await mysqlPool.getConnection();
    try {
      const whereClause = userId ? 'WHERE user_id = ?' : '';
      const params = userId ? [userId, limit, offset] : [limit, offset];

      const [rows] = await connection.execute<RowDataPacket[]>(
        `SELECT * FROM documents ${whereClause} 
         ORDER BY upload_time DESC LIMIT ? OFFSET ?`,
        params
      );

      return rows.map(row => ({
        ...row,
        metadata:
          typeof row.metadata === 'string'
            ? JSON.parse(row.metadata)
            : row.metadata,
      })) as Document[];
    } finally {
      connection.release();
    }
  }

  // 按内容搜索文档
  static async searchByContent(
    query: string,
    userId?: string,
    limit = 10
  ): Promise<Document[]> {
    const connection = await mysqlPool.getConnection();
    try {
      let sql =
        'SELECT * FROM documents WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)';
      const params: any[] = [query];

      if (userId) {
        sql += ' AND user_id = ?';
        params.push(userId);
      }

      sql += ' ORDER BY upload_time DESC LIMIT ?';
      params.push(limit);

      const [rows] = await connection.execute<RowDataPacket[]>(sql, params);
      return rows.map(row => ({
        ...row,
        metadata:
          typeof row.metadata === 'string'
            ? JSON.parse(row.metadata)
            : row.metadata,
      })) as Document[];
    } finally {
      connection.release();
    }
  }

  // 按标题搜索文档
  static async searchByTitle(
    query: string,
    userId?: string,
    limit = 10
  ): Promise<Document[]> {
    const connection = await mysqlPool.getConnection();
    try {
      let sql = 'SELECT * FROM documents WHERE title LIKE ?';
      const params: any[] = [`%${query}%`];

      if (userId) {
        sql += ' AND user_id = ?';
        params.push(userId);
      }

      sql += ' ORDER BY upload_time DESC LIMIT ?';
      params.push(limit);

      const [rows] = await connection.execute<RowDataPacket[]>(sql, params);
      return rows.map(row => ({
        ...row,
        metadata:
          typeof row.metadata === 'string'
            ? JSON.parse(row.metadata)
            : row.metadata,
      })) as Document[];
    } finally {
      connection.release();
    }
  }

  // 根据ID获取文档
  static async getById(id: string): Promise<Document | null> {
    const connection = await mysqlPool.getConnection();
    try {
      const [rows] = await connection.execute<RowDataPacket[]>(
        'SELECT * FROM documents WHERE id = ?',
        [id]
      );

      if (rows.length === 0) return null;

      const row = rows[0];
      return {
        ...row,
        metadata:
          typeof row.metadata === 'string'
            ? JSON.parse(row.metadata)
            : row.metadata,
      } as Document;
    } finally {
      connection.release();
    }
  }

  // 删除文档
  static async delete(id: string): Promise<boolean> {
    const connection = await mysqlPool.getConnection();
    try {
      const [result] = await connection.execute<ResultSetHeader>(
        'DELETE FROM documents WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  // 更新文档状态
  static async updateStatus(
    id: string,
    status: 'processing' | 'completed' | 'failed'
  ): Promise<boolean> {
    const connection = await mysqlPool.getConnection();
    try {
      const [result] = await connection.execute<ResultSetHeader>(
        'UPDATE documents SET status = ? WHERE id = ?',
        [status, id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }
}

// 聊天会话操作
export class ChatSessionOperations {
  // 创建会话
  static async create(title: string, userId?: string): Promise<string> {
    const connection = await mysqlPool.getConnection();
    try {
      const [result] = await connection.execute<ResultSetHeader>(
        'INSERT INTO chat_sessions (title, user_id) VALUES (?, ?)',
        [title, userId || null]
      );

      const [rows] = await connection.execute<RowDataPacket[]>(
        'SELECT id FROM chat_sessions WHERE id = LAST_INSERT_ID()'
      );

      return rows[0]?.id || '';
    } finally {
      connection.release();
    }
  }

  // 获取会话列表
  static async getAll(userId?: string): Promise<ChatSession[]> {
    const connection = await mysqlPool.getConnection();
    try {
      const whereClause = userId ? 'WHERE user_id = ?' : '';
      const params = userId ? [userId] : [];

      const [rows] = await connection.execute<RowDataPacket[]>(
        `SELECT * FROM chat_sessions ${whereClause} ORDER BY updated_at DESC`,
        params
      );

      return rows as ChatSession[];
    } finally {
      connection.release();
    }
  }

  // 根据用户ID获取会话列表（带分页）
  static async getByUserId(
    userId: string,
    limit = 50,
    offset = 0
  ): Promise<ChatSession[]> {
    const connection = await mysqlPool.getConnection();
    try {
      const [rows] = await connection.execute<RowDataPacket[]>(
        'SELECT * FROM chat_sessions WHERE user_id = ? ORDER BY updated_at DESC LIMIT ? OFFSET ?',
        [userId, limit, offset]
      );

      return rows as ChatSession[];
    } finally {
      connection.release();
    }
  }

  // 获取会话详情（包含消息）
  static async getById(id: string): Promise<ChatSession | null> {
    const connection = await mysqlPool.getConnection();
    try {
      // 获取会话信息
      const [sessionRows] = await connection.execute<RowDataPacket[]>(
        'SELECT * FROM chat_sessions WHERE id = ?',
        [id]
      );

      if (sessionRows.length === 0) return null;

      // 获取会话消息
      const [messageRows] = await connection.execute<RowDataPacket[]>(
        'SELECT * FROM chat_messages WHERE session_id = ? ORDER BY timestamp ASC',
        [id]
      );

      const session = sessionRows[0] as ChatSession;
      session.messages = messageRows.map(row => ({
        ...row,
        metadata:
          typeof row.metadata === 'string'
            ? JSON.parse(row.metadata)
            : row.metadata,
      })) as ChatMessage[];

      return session;
    } finally {
      connection.release();
    }
  }

  // 删除会话
  static async delete(id: string): Promise<boolean> {
    const connection = await mysqlPool.getConnection();
    try {
      const [result] = await connection.execute<ResultSetHeader>(
        'DELETE FROM chat_sessions WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }
}

// 聊天消息操作
export class ChatMessageOperations {
  // 添加消息
  static async create(
    message: Omit<ChatMessage, 'id' | 'timestamp'>
  ): Promise<string> {
    const connection = await mysqlPool.getConnection();
    try {
      const messageId = crypto.randomUUID();
      const [result] = await connection.execute<ResultSetHeader>(
        'INSERT INTO chat_messages (id, session_id, role, content, metadata) VALUES (?, ?, ?, ?, ?)',
        [
          messageId,
          message.session_id,
          message.role,
          message.content,
          JSON.stringify(message.metadata || {}),
        ]
      );

      return messageId;
    } finally {
      connection.release();
    }
  }

  // 获取会话消息
  static async getBySessionId(
    sessionId: string,
    limit = 50,
    offset = 0
  ): Promise<ChatMessage[]> {
    const connection = await mysqlPool.getConnection();
    try {
      const [rows] = await connection.execute<RowDataPacket[]>(
        'SELECT * FROM chat_messages WHERE session_id = ? ORDER BY created_at ASC LIMIT ? OFFSET ?',
        [sessionId, limit, offset]
      );

      return rows.map(row => ({
        ...row,
        metadata:
          typeof row.metadata === 'string'
            ? JSON.parse(row.metadata)
            : row.metadata,
      })) as ChatMessage[];
    } finally {
      connection.release();
    }
  }
}

// 文档块操作
export class DocumentChunkOperations {
  // 创建文档块
  static async create(
    documentId: string,
    chunkIndex: number,
    content: string,
    qdrantPointId: string,
    metadata?: any
  ): Promise<string> {
    const connection = await mysqlPool.getConnection();
    try {
      const [result] = await connection.execute<ResultSetHeader>(
        'INSERT INTO document_chunks (document_id, chunk_index, content, qdrant_point_id, metadata) VALUES (?, ?, ?, ?, ?)',
        [
          documentId,
          chunkIndex,
          content,
          qdrantPointId,
          JSON.stringify(metadata || {}),
        ]
      );

      const [rows] = await connection.execute<RowDataPacket[]>(
        'SELECT id FROM document_chunks WHERE id = LAST_INSERT_ID()'
      );

      return rows[0]?.id || '';
    } finally {
      connection.release();
    }
  }

  // 根据文档ID获取所有块
  static async getByDocumentId(documentId: string): Promise<any[]> {
    const connection = await mysqlPool.getConnection();
    try {
      const [rows] = await connection.execute<RowDataPacket[]>(
        'SELECT * FROM document_chunks WHERE document_id = ? ORDER BY chunk_index ASC',
        [documentId]
      );

      return rows.map(row => ({
        ...row,
        metadata:
          typeof row.metadata === 'string'
            ? JSON.parse(row.metadata)
            : row.metadata,
      }));
    } finally {
      connection.release();
    }
  }

  // 删除文档的所有块
  static async deleteByDocumentId(documentId: string): Promise<boolean> {
    const connection = await mysqlPool.getConnection();
    try {
      const [result] = await connection.execute<ResultSetHeader>(
        'DELETE FROM document_chunks WHERE document_id = ?',
        [documentId]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }
}
